package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Calendar;

/**
 * The Data Access Object for the webfarm, used to request and add
 * humidity and temperature data. 
 * @author INTI3D
 *
 */
public class HumiTempDao {
	private String sqlHumiRead = "SELECT * FROM HumiTemp where type = 'H' AND time > ? AND time < ?";
	private String sqlHumiWrite = "INSERT INTO HumiTemp(time, type, value) VALUES(?, 'H', ?)";
	private String sqlTempRead = "SELECT * FROM HumiTemp where type = 'T' AND time > ? AND time < ?";
	private String sqlTempWrite = "INSERT INTO HumiTemp(time, type, value) VALUES(?, 'T', ?)";
	private String sqlHighest = "SELECT * from HUMITEMP where TIME=(SELECT max(TIME) from HUMITEMP where TYPE=?)";
	
	private Connection        con			= null ;
	private PreparedStatement psReadHumi	= null ;
	private PreparedStatement psWriteHumi	= null ;
	private PreparedStatement psReadTemp	= null ;
	private PreparedStatement psWriteTemp	= null ;
	private PreparedStatement psHighest		= null ;
	
	
	public HumiTempDao(){
		DBmanager myDb = DBmanager.getInstance();
		con = myDb.getConnection();
		try{
			this.psReadHumi		= con.prepareStatement(sqlHumiRead);
			this.psWriteHumi	= con.prepareStatement(sqlHumiWrite);
			this.psReadTemp		= con.prepareStatement(sqlTempRead);
			this.psWriteTemp	= con.prepareStatement(sqlTempWrite);
			this.psHighest	= con.prepareStatement(sqlHighest);
		} catch(SQLException se) {
			printSQLException(se) ;
		}
	}
	
	/**
	 * get the last record of a specified type
	 * 
	 * @param typ the type you want
	 * @return the value
	 */
	public double getLast(char typ){
		try {
			psHighest.clearParameters();
			psHighest.setString(1, ""+typ);
			ResultSet rs = psHighest.executeQuery();
			if(rs.next()){
				return rs.getDouble("value");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	
	/**
	 * add a record for humidity
	 * 
	 * @param val The humidity value
	 */
	public void addHumi(double val){
		try {
			psWriteHumi.clearParameters();
			Calendar c = Calendar.getInstance();
			psWriteHumi.setTimestamp(1, new Timestamp(c.getTimeInMillis()));
			psWriteHumi.setDouble(2, val);
			int resultaat = psWriteHumi.executeUpdate();
			if (resultaat == 0) {
				System.out.println("nix done");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * add a record for temperature
	 * 
	 * @param val The temperature value
	 */
	public void addTemp(double val){
		try {
			psWriteTemp.clearParameters();
			Calendar c = Calendar.getInstance();
			psWriteTemp.setTimestamp(1, new Timestamp(c.getTimeInMillis()));
			psWriteTemp.setDouble(2, val);
			int resultaat = psWriteTemp.executeUpdate();
			if (resultaat == 0) {
				System.out.println("nix done");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
	
	/**
	 * retrieve all temperatures between two provided dates
	 * 
	 * @param t The minimum Timestamp
	 * @param t2 The maximum Timestamp
	 * @return The arraylist containing all temperatures requested
	 */
	public ArrayList<HumiTempBean> getTemp(Timestamp t, Timestamp t2){
		try {
			ArrayList<HumiTempBean> list = new ArrayList<HumiTempBean>();
			psReadTemp.clearParameters();
			psReadTemp.setTimestamp(1, t);
			psReadTemp.setTimestamp(2, t2);
			ResultSet rs = psReadTemp.executeQuery();
			while(rs.next()){
				HumiTempBean ht = new HumiTempBean();
				ht.setAll(rs.getTimestamp("time"), 'T', rs.getDouble("value"));
				list.add(ht);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * retrieve all humidities between two provided dates
	 * 
	 * @param t The minimum Timestamp
	 * @param t2 The maximum Timestamp
	 * @return The arraylist containing all humidities requested
	 */
	public ArrayList<HumiTempBean> getHumi(Timestamp t, Timestamp t2){
		try {
			ArrayList<HumiTempBean> list = new ArrayList<HumiTempBean>();
			psReadHumi.clearParameters();
			psReadHumi.setTimestamp(1, t);
			psReadHumi.setTimestamp(2, t2);
			ResultSet rs = psReadHumi.executeQuery();
			while(rs.next()){
				HumiTempBean ht = new HumiTempBean();
				System.out.println(rs.getTimestamp("time"));
				ht.setAll(rs.getTimestamp("time"), 'H', rs.getDouble("value"));
				list.add(ht);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}
	
	/**
	 * prints an sql exeption, for debugging purposes
	 * 
	 * @param se the exeption generated
	 */
	private void printSQLException(SQLException se) {
		while(se != null) {

			System.out.print("SQLException: State:   " + se.getSQLState());
			System.out.println("Severity: " + se.getErrorCode());
			System.out.println(se.getMessage());			

			se = se.getNextException();
		}
	}	
}
